# -*- coding: UTF-8 -*-
import xlrd
import MySQLdb


db = MySQLdb.connect("localhost","root","123","cadastro" )
cursor = db.cursor()


wb = xlrd.open_workbook('cadastro2.xls')

wb.sheet_names()

sh = wb.sheet_by_name(u'Plan2')

#for rownum in range(sh.nrows):
	#print sh.row_values(rownum)
	
first_column = sh.col_values(0)
second_column = sh.col_values(1)
third_column = sh.col_values(2)
fourth_column = sh.col_values(3)
fifth_column = sh.col_values(4)
#print first_column

register = {}

for i in range(len(first_column)):
	name = first_column[i]
	bairro = second_column[i]
	telefone = third_column[i]
	paroquia = fourth_column[i]
	email = fifth_column[i]
	
	if isinstance(telefone, float):
		telefone = int(telefone)
		telefone = str(telefone)
	
	if email != '':
		if email[-1] == ";":
			email = email[0:-1]
	
	name = '"'+name+'"'
	if bairro != '': 
		bairro = ',"'+bairro+'"'
		bairrosql = ',bairro'
	else:
		bairrosql = ''
	if telefone != '': 
		telefone = ',"'+telefone+'"'
		telefonesql = ',telefone'
	else:
		telefonesql = ""
	if paroquia != '': 
		paroquia = ',"'+paroquia+'"'
		paroquiasql = ',paroquia'
	else:
		paroquiasql = ""
	if email != '': 
		email = ',"'+email+'"'
		emailsql = ',email'
	else:
		emailsql = ""
	
	sql ='INSERT INTO inscritos (nome' + bairrosql + telefonesql + paroquiasql + emailsql +', sexo) VALUES('	
	
	#paroquia = MySQLdb.escape_string(paroquia)
	#name = MySQLdb.escape_string(name)
	#paroquia.replace("'", "\'")
	#name.replace("'", "\'")
	
	values = name + bairro + telefone + paroquia + email + ', "0")'

	sql = sql +  values
	#print sql
	
	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except MySQLdb.Error,e:
		# Rollback in case there is any error
		print e
		print "NUMBER: "+ str(i)
		print paroquia
		print name
		db.rollback()
	

db.close()